<?php

namespace App\Http\Controllers;

use Illuminate\Http\Request;

use App\Http\Requests;
use Illuminate\Support\Facades\DB;

class SelectController extends Controller
{
    //查询构建器训练

    public function index(Request $request){
        $key=$request['key'];
        $data=$this->$key();
        return $data;

    }

    /**
     * get 获取多条数据集
     *
     * @return \Illuminate\Http\JsonResponse
     */
    public function get(){
        $data=DB::table('user')->get();
        return response()->json($data);
    }

    /**
     * first 获取一条数据
     *
     * @return \Illuminate\Http\JsonResponse
     */
    public function first(){
        $data=DB::table('user')->where('user_name','大美')->first();
        return response()->json($data);
    }

    /**
     * chunk 这个没闹明白怎么用
     *
     * @return \Illuminate\Http\JsonResponse
     */
    public function chunk(){
        $data=DB::table('user')->chunk(2, function($users) {
            foreach ($users as $user) {
                //
                //$users[$k]['hh']='hhh';
            }
        });
        return response()->json($data);//true
    }

    /**
     * lists 获取一列数据
     *
     * @return mixed
     */
    public function lists(){
        $data=DB::table('user')->lists('age');
        return $data;
    }

    /**
     * lists 获取一列数据
     * 如果lists传入两个字段名，后面的字段作为键，前面的字段作为值
     *
     * @return mixed
     */
    public function listsKey(){
        $data=DB::table('user')->lists('age','user_name');
        return $data;
    }

    /*聚合查询*/

    /**
     * count 查询数据条数
     *
     * @return mixed
     */
    public function count(){
        $data=DB::table('user')->count();
        return $data;
    }

    /**
     * max 查询某列数据最大值
     *
     * @return mixed
     */
    public function max(){
        $data=DB::table('user')->max('age');
        return $data;
    }

    /**
     * min 查询某列数据最小值
     *
     * @return mixed
     */
    public function min(){
        $data=DB::table('user')->min('age');
        return $data;
    }

    /**
     * avg 查询某列数据平均值
     *
     * @return mixed
     */
    public function avg(){
        $data=DB::table('user')->avg('age');
        return $data;
    }

    /**
     * sum 查询某列数据和
     *
     * @return mixed
     */
    public function sum(){
        $data=DB::table('user')->sum('user_name');
        return $data;
    }

    /**
     * select 查询指定字段
     *
     * @return mixed
     */
    public function select(){
        $data=DB::table('user')->select('id','user_name')->get();
        return $data;
    }

    /**
     * distinct 查询非重复值
     *
     * @return mixed
     */
    public function distinct(){
        $data=DB::table('user')->distinct()->get();
        return $data;
    }

    /**
     * addSelect 增加要查询的字段
     *
     * @return mixed
     */
    public function addSelect(){
        $data=DB::table('user')->select('id')->addSelect('user_name')->get();
        return $data;
    }

    /**
     * raw 原生查询
     *
     * @return mixed
     */
    public function row(){
        $data=DB::table('user')->select(DB::raw('count(`id`) as count','sum(age) as sum'))
            /*->addSelect('user_name')*/->get();
        return $data;
    }
}
